<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Frameset//EN">
<html>
<head>
<title>How to use join table</title>
    <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1" />
    <meta name="generator" content="Doc-O-Matic" />
    <meta http-equiv="Content-Style-Type" content="text/css" />
    <link rel="STYLESHEET" href="default.css" type="text/css" />

<script type="text/javascript" src="scripts.js"></script>
</head>
<body class="Element700" onload="onBodyLoadEx('frames.html', 'topic', 'How_to_use_join_table.html');" onmousedown="onBodyMouseDown();">

<!-- Begin Popups -->

<!-- End Popups -->

<!-- Begin Page Header -->
<div class="Element710" id="areafixed">
<div class="Element94">
<em>You are here:</em> Using General SQL Parser &gt; <a href="How_to_use_join_table.html" target="topic">How to use join table</a></div>
<div class="Element92">
<table width="100%" cellspacing="0" cellpadding="0">
<tr><td width="50%">
<div class="Element2">
<a href="contents.html" target="tocidx"><img src="btn_globals_contents_midblue.gif" border="0" alt="Contents" title="Contents" onmouseover="switchImage(this, 'btn_globals_contents_midblue_hover.gif');" onmouseout="switchImage(this, 'btn_globals_contents_midblue.gif');"></a><a href="idx.html" target="tocidx"><img src="btn_globals_index_midblue.gif" border="0" alt="Index" title="Index" onmouseover="switchImage(this, 'btn_globals_index_midblue_hover.gif');" onmouseout="switchImage(this, 'btn_globals_index_midblue.gif');"></a><a href="index.html" target="topic"><img src="btn_globals_home_midblue.gif" border="0" alt="Home" title="Home" onmouseover="switchImage(this, 'btn_globals_home_midblue_hover.gif');" onmouseout="switchImage(this, 'btn_globals_home_midblue.gif');"></a></div>
</td><td width="50%">
<div class="Element90">
<a href="Getting_Started.html" target="topic"><img src="btn_prev_midblue.gif" border="0" alt="Previous" title="Previous" onmouseover="switchImage(this, 'btn_prev_midblue_hover.gif');" onmouseout="switchImage(this, 'btn_prev_midblue.gif');"></a><img src="btn_up_d.gif" border="0" alt="Up" title="Up"><a href="Visitor_pattern_used_in_General_SQL_Parser.html" target="topic"><img src="btn_next_midblue.gif" border="0" alt="Next" title="Next" onmouseover="switchImage(this, 'btn_next_midblue_hover.gif');" onmouseout="switchImage(this, 'btn_next_midblue.gif');"></a></div>
</td></tr></table><div class="Element5">
How to use join table</div>
</div>
</div>

<!-- End Page Header -->

<!-- Begin Client Area -->
<div class="Element720" id="areascroll">
<div class="Element721">

<!-- Begin Page Content -->
<div class="Element58">
<a name="Description"></a><div class="Element11">
<div class="Element10">
<p class="Element10">
 &nbsp;</p>
<p class="Element10">
From the following 3 diagrams, you may have a better understand of <a href="TLzJoin.html" target="topic">TLzJoin</a> , <a href="TLzJoinList.html" target="topic">TLzJoinList</a>, <a href="TLzJoinItem.html" target="topic">TLzJoinItem</a>, <a href="TLzJoinItemList.html" target="topic">TLzJoinItemList</a> which is used to represent join table in from clause.&nbsp;</p>
<p class="Element10">
&nbsp;</p>
<p class="Element10">
You can check <a href="TSelectSqlStatement_JoinTables.html" target="topic">TSelectSqlStatement.JoinTables</a> which is type of <a href="TLzJoinList.html" target="topic">TLzJoinList</a> to start fetching all those information.&nbsp;</p>
<p class="Element10">
&nbsp;</p>
<p class="Element10">
<img src="jointable1.jpg" border="0" alt="" title="">&nbsp;</p>
<p class="Element10">
<img src="jointable2.jpg" border="0" alt="" title=""> <img src="jointable3.jpg" border="0" alt="" title="">&nbsp;</p><div class="Element15">
Scenario 1</div>
<p class="Element10">
&nbsp;</p>
<div class="Element13"><div class="Element12"><pre class="Element12">Select t1.f1
from my.table1 t1,my.table2 t2
where t1.f1 = t2.f1</pre></div></div>
<p class="Element10">
&nbsp;</p>
<p class="Element10">
After parsing this sql, information in TSelectSqlStatement.Tables is:&nbsp;</p>
<p class="Element10">
&nbsp;</p><div class="Element13"><div class="Element12"><pre class="Element12">Tables.Count = 2;

Tables[0].TableName = 'table1';
Tables[0].TablePrefix = 'my';
Tables[0].TableAlias = 't1';

Tables[1].TableName = 'table2';
Tables[1].TablePrefix = 'my';
Tables[1].TableAlias = 't2';

And information in <a href="TSelectSqlStatement_JoinTables.html" target="topic">TSelectSqlStatement.JoinTables</a> is:
JoinTables.Count = 2;

JoinTables.items[0].JoinTableType = jttTable; // the collection of join is table : my.table1
JoinTables.items[0].JoinTable = Tables[0];//my.table1
JoinTables.items[0].JoinItems.count = 0;

JoinTables.items[1].JoinTableType = jttTable; // the collection of join is table : my.table1
JoinTables.items[1].JoinTable = Tables[1];//my.table2
JoinTables.items[1].JoinItems.count = 0;
</pre></div></div>
<p class="Element10">
&nbsp;</p>
<div class="Element15">
Scenario 2</div>
<div class="Element13"><div class="Element12"><pre class="Element12">Select t1.f1
from my.table1 t1
join my.table2 t2
on t1.f1 = t2.f1</pre></div></div>
<p class="Element10">
&nbsp;</p>
<p class="Element10">
After parsing this sql, information in TSelectSqlStatement.Tables is:&nbsp;</p>
<p class="Element10">
&nbsp;</p><div class="Element13"><div class="Element12"><pre class="Element12">Tables.Count = 2;

Tables[0].TableName = 'table1';
Tables[0].TablePrefix = 'my';
Tables[0].TableAlias = 't1';

Tables[1].TableName = 'table2';
Tables[1].TablePrefix = 'my';
Tables[1].TableAlias = 't2';</pre></div></div>
<p class="Element10">
&nbsp;</p>
<p class="Element10">
everything is the same as scenario 1, Now, check the information in JoinTables:&nbsp;</p>
<p class="Element10">
&nbsp;</p><div class="Element13"><div class="Element12"><pre class="Element12">JoinTables.Count = 1;

JoinTables.items[0].JoinTableType = jttTable; // the collection of join is table : my.table1
JoinTables.items[0].JoinTable = Tables[0];//my.table1
JoinTables.items[0].JoinItems.count = 1;

JoinTables.items[0].JoinItems[0].JoinItemTableType = jttTable;//the collection of this joinitem is table:my.table2
JoinTables.items[0].JoinItems[0].JoinItemTable = my.table2;
JoinTables.items[0].JoinItems[0].JoinType = sjtjoin;
JoinTables.items[0].JoinItems[0].JoinQualType = sjqOn;
JoinTables.items[0].JoinItems[0].JoinQual = AEXPRESSION ; //a custom expressoin point to t1.f1 = t2.f1</pre></div></div>
<p class="Element10">
&nbsp;</p>
<p class="Element10">
&nbsp;</p>
<div class="Element15">
Scenario 3</div>
<p class="Element10">
&nbsp;</p>
<div class="Element13"><div class="Element12"><pre class="Element12">select t1.f1
from my.table1 t1
 join (my.table2 t2
 left join my.table3 t3
 on t2.f1 = t3.f1) as joinalias1
 on t1.f1 = t2.f1;</pre></div></div>
<p class="Element10">
&nbsp;</p>
<p class="Element10">
After parsing this sql, information in Tables is:&nbsp;</p>
<p class="Element10">
&nbsp;</p><div class="Element13"><div class="Element12"><pre class="Element12">Tables.Count = 3;

Tables[0].TableName = 'table1';
Tables[0].TablePrefix = 'my';
Tables[0].TableAlias = 't1';

Tables[1].TableName = 'table2';
Tables[1].TablePrefix = 'my';
Tables[1].TableAlias = 't2';

Tables[2].TableName = 'table3';
Tables[2].TablePrefix = 'my';
Tables[2].TableAlias = 't3';</pre></div></div>
<p class="Element10">
&nbsp;</p>
<p class="Element10">
Now, checking the information in JoinTables:&nbsp;</p>
<p class="Element10">
&nbsp;</p><div class="Element13"><div class="Element12"><pre class="Element12">JoinTables.Count = 1;


JoinTables.items[0].JoinTableType = jttTable; // the collection of join is table : my.table1
JoinTables.items[0].JoinTable = Tables[0];//my.table1
JoinTables.items[0].JoinItems.count = 1;

JoinTables.items[0].JoinItems[0].JoinItemTableType = jttJoin;//the collection of this joinitem is join
JoinTables.items[0].JoinItems[0].JoinItemJoin = AJoin; // ajoin point to (my.table2 t2
left join my.table3 t3
on t2.f1 = t3.f1) as joinalias1

JoinTables.items[0].JoinItems[0].JoinType = sjtjoin;
JoinTables.items[0].JoinItems[0].JoinQualType = sjqOn;
JoinTables.items[0].JoinItems[0].JoinQual = AEXPRESSION ; //a custom expressoin point to t1.f1 = t2.f1


JoinTables.items[0].JoinItems[0].JoinItemJoin.JoinTableType = jttTable;
JoinTables.items[0].JoinItems[0].JoinItemJoin.JoinTable = Tables[1]; //my.table2
JoinTables.items[0].JoinItems[0].JoinItemJoin.Alias = joinalias1
JoinTables.items[0].JoinItems[0].JoinItemJoin.AliasWithAs = true;
JoinTables.items[0].JoinItems[0].JoinItemJoin.NestedLevel = 1;
JoinTables.items[0].JoinItems[0].JoinItemJoin.JoinItems.count = 1;

JoinTables.items[0].JoinItems[0].JoinItemJoin.JoinItems[0].JoinItemTableType = jttTable;
JoinTables.items[0].JoinItems[0].JoinItemJoin.JoinItems[0].JoinItemTable = Tables[2]; //my.table3
JoinTables.items[0].JoinItems[0].JoinItemJoin.JoinItems[0].JoinType = sjtleft;
JoinTables.items[0].JoinItems[0].JoinItemJoin.JoinItems[0].JoinQualType = jqtOn;
JoinTables.items[0].JoinItems[0].JoinItemJoin.JoinItems[0].JoinQual = AEXPRESSION ; //a custom expressoin point to t2.f1 = t3.f1
</pre></div></div>
<div class="Element15">
Scenario 4</div>
<div class="Element13"><div class="Element12"><pre class="Element12">select t1.f1
from my.table1 t1
 right join ((my.table2 t2
 left outer join my.table3 t3
 on (t2.f1 = t3.f2))
 left join (my.table4 t4
 full outer join my.table5 t5
 on (t4.f1 = t5.f1)) t4alias
 on (t4.b1 = t2.c1))
 on (t1.a1 = t3.b3);</pre></div></div>
<p class="Element10">
&nbsp;</p>
<p class="Element10">
After parsing this sql, information in Tables is:&nbsp;</p>
<p class="Element10">
&nbsp;</p><div class="Element13"><div class="Element12"><pre class="Element12">Tables.Count = 5;

Tables[0].TableName = 'table1';
Tables[0].TablePrefix = 'my';
Tables[0].TableAlias = 't1';

Tables[1].TableName = 'table2';
Tables[1].TablePrefix = 'my';
Tables[1].TableAlias = 't2';

Tables[2].TableName = 'table3';
Tables[2].TablePrefix = 'my';
Tables[2].TableAlias = 't3';

Tables[3].TableName = 'table4';
Tables[3].TablePrefix = 'my';
Tables[3].TableAlias = 't4';

Tables[4].TableName = 'table5';
Tables[4].TablePrefix = 'my';
Tables[4].TableAlias = 't5';
</pre></div></div>
<p class="Element10">
&nbsp;</p>
<p class="Element10">
Now, checking the information in JoinTables:&nbsp;</p>
<p class="Element10">
&nbsp;</p><div class="Element13"><div class="Element12"><pre class="Element12">JoinTables.Count = 1;

JoinTables.items[0].JoinTableType = jttTable;
JoinTables.items[0].JoinTable = Tables[0]; //my.table1

JoinTables.items[0].JoinItems.count = 1;

JoinTables.items[0].JoinItems[0].JoinType = sjtrightjoin;
JoinTables.items[0].JoinItems[0].JoinQualType = sjqOn;
JoinTables.items[0].JoinItems[0].JoinQual = AEXPRESSION ; //a custom expressoin point to t1.a1 = t3.b3

JoinTables.items[0].JoinItems[0].JoinItemType = jttJoin;
JoinTables.items[0].JoinItems[0].JoinItemJoin = AJoin; // ((my.table2 t2
left outer join my.table3 t3
on (t2.f1 = t3.f2))
left join (my.table4 t4
full outer join my.table5 t5
on (t4.f1 = t5.f1)) t4alias
on (t4.b1 = t2.c1))



AJoin.NestedLevel = 1;
AJoin.JoinTableType = jttJoin;
AJoin.JoinJoin = ASubJoin; //(my.table2 t2
left outer join my.table3 t3
on (t2.f1 = t3.f2))

ASubJoin.NestedLevel = 1;
ASubJoin.JoinTableType = jttTable;
ASubJoin.JoinTable = Tables[1]; //my.table2
ASubJoin.JoinItems.count = 1;
ASubJoin.JoinItems.items[0].JoinType = sjtleftjoin;
ASubJoin.JoinItems.items[0].JoinQualType = jtqon;
ASubJoin.JoinItems.items[0].JoinQual = AExpression;//t2.f1 = t3.f2
ASubJoin.JoinItems.items[0].JoinItemTableType = jttTable;
ASubJoin.JoinItems.items[0].JoinItemTable = Tables[2]; //my.table3


AJoin.JoinItems.count = 1;

AJoin.JoinItems.items[0].JoinTableType = jttJoin;//again, this is a join
AJoin.JoinItems.items[0].JoinJoin = ASub2Join;//(my.table4 t4
full outer join my.table5 t5
on (t4.f1 = t5.f1)) t4alias

ASub2Join.Alias = t4alias;
ASub2Join.NestedLevel = 1;
ASub2Join.JoinTableType = jttTable;
ASub2Join.JoinTable = Tables[3]; //my.table4
ASub2Join.JoinItems.count = 1;
ASub2Join.JoinItems.items[0].JoinType = sjtfullOuter;
ASub2Join.JoinItems.items[0].JoinQualType = jtqon;
ASub2Join.JoinItems.items[0].JoinQual = AExpression;//t4.f1 = t5.f1
ASub2Join.JoinItems.items[0].JoinItemTableType = jttTable;
ASubJoin.JoinItems.items[0].JoinItemTable = Tables[4]; //my.table5

AJoin.JoinItems.items[0].JoinType = sjtleftouter;//left join
AJoin.JoinItems.items[0].JoinQualType = sjqOn;
AJoin.JoinItems.items[0].JoinQual = sjqOn;// t4.b1 = t2.c1</pre></div></div>
<p class="Element10">
&nbsp;</p>
<p class="Element10">
You may also check this demo:&nbsp;</p>
<p class="Element10">
<a href="http://www.sqlparser.com/dl/samples/jointabledetail.zip">http://www.sqlparser.com/dl/samples/jointabledetail.zip</a>&nbsp;</p></div>
</div>
</div>
<!-- End Page Content -->

<!-- Begin Page Footer -->
<div class="Element93">
<table width="100%" cellspacing="0" cellpadding="0">
<tr><td width="100%">
<div class="Element3">
Gudu software http://www.sqlparser.com</div>
</td></tr><tr><td width="100%">
<div class="Element97">
Send <a href="#" onclick="sendFeedback('info@sqlparser.com', 'Documentation Feedback', 'Project: General SQL Parser Help file%0ATopic ID: How to use join table%0ATitle: How to use join table');">comments</a> about this topic.</div>
</td></tr></table></div>

<!-- End Page Footer -->
</div>
</div>

<!-- End Client Area -->
</body></html>